using System;
using System.Collections.Generic;
using System.Text;
using FinanceStats.Entities;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using FinanceStats.Common;
using System.Data.Common;

namespace FinanceStats.DAL
{
    public static class OutcomeItemsDAO
    {

        private static string insertSQL = "INSERT INTO ACS.DBO.OutcomeItem (ItemName, GroupID) VALUES (@ItemName, @GroupID) SET @ItemID = SCOPE_IDENTITY()";
        private static string updateSQL = "UPDATE ACS.DBO.OutcomeItem SET ItemName = @ItemName, GroupID = @GroupID WHERE ItemID = @ItemID";

        private static DbCommand insertCommand = null;
        private static DbCommand updateCommand = null;

        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                insertCommand = db.GetSqlStringCommand(insertSQL);
                db.AddInParameter(insertCommand, "@ItemName", DbType.String);
                db.AddInParameter(insertCommand, "@GroupID", DbType.Int32);
                db.AddOutParameter(insertCommand, "@ItemID", DbType.Int32, 4);
            }
            return insertCommand;
        }

        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (updateCommand == null)
            {
                updateCommand = db.GetSqlStringCommand(updateSQL);
                db.AddInParameter(updateCommand, "@ItemName", DbType.String);
                db.AddInParameter(updateCommand, "@GroupID", DbType.Int32);
                db.AddInParameter(updateCommand, "@ItemID", DbType.Int32);
            }
            return updateCommand;
        }


        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, OutcomeItem oi)
        {
            db.SetParameterValue(cmd, "@ItemName", oi.ItemName);
            db.SetParameterValue(cmd, "@GroupID", (int)oi.GroupID);
        }

        private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, OutcomeItem oi)
        {
            db.SetParameterValue(cmd, "@ItemName", oi.ItemName);
            db.SetParameterValue(cmd, "@GroupID", (int)oi.GroupID);
            db.SetParameterValue(cmd, "@ItemID", (int)oi.ItemID);
        }

        public static List<OutcomeItem> LoadOutcomeItems()
        {
            return LoadOutcomeItems(-1);
        }

        public static List<OutcomeItem> LoadOutcomeItems(int groupID)
        {
            List<OutcomeItem> res = new List<OutcomeItem>();
            string sql = "SELECT ItemID, GroupID, ItemName FROM ACS.DBO.OutcomeItem {0} ORDER BY ItemName";
            if (groupID != -1)
            {
                sql = String.Format(sql, " WHERE GroupID = " + groupID);
            }
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        OutcomeItem oi = new OutcomeItem();
                        oi.ItemID = reader.GetInt32(0);
                        oi.GroupID = reader.GetInt32(1);
                        oi.ItemName = reader.IsDBNull(2) ? String.Empty : reader.GetString(2);
                        res.Add(oi);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static Dictionary<string, int> LoadNamesDictionary()
        {
            Dictionary<string, int> res = new Dictionary<string, int>();
            string sql = "SELECT ItemID, ItemName FROM ACS.DBO.OutcomeItem ORDER BY ItemName";
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        int itemID = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        if (res.ContainsKey(name.ToLower())) // amibigious name, remove item
                        {
                            res.Remove(name.ToLower());
                        }
                        else 
                        {
                            res.Add(name.ToLower(), itemID);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static bool InsertOutcomeItem(OutcomeItem oi)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, oi);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                oi.ItemID = (int)db.GetParameterValue(cmd, "@ItemID");
            }
            return res;
        }

        public static bool UpdateOutcomeItem(OutcomeItem oi)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DbCommand cmd = GetUpdateCommand(db);
            SetUpdateValues(db, cmd, oi);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
    }
}
